﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
///Class1 的摘要说明
/// </summary>
/// 
namespace ClassLibraryDatabase
{
	public class DataBaseClass
	{

		public DataBaseClass()
		{
			//
			//TODO: 在此处添加构造函数逻辑
			//
		}

		/// <summary>
		/// 更新方法
		/// </summary>
		/// <param name="name">属性名字字段数组</param>
		/// <param name="value">属性值数组</param>
		/// <param name="tableName">操作数据表的名字</param>
		/// <param name="conStr">连接字符串</param>
		public static bool Update(string[] name, string[] value, string tableName, string conStr)
		{
			//sql语句中含属性名字段的部分字符串
			string partialStr = "";
			// Create connection, command
			SqlCommand cmd = GetSQLCommand(conStr);
			SqlConnection con = cmd.Connection;

			for (int i = 1; i < name.Length; i++)
			{
				if (i != 1)
					partialStr += ",";
				partialStr = partialStr + name[i] + "=@" + name[i];
			}
			cmd.CommandText = "UPDATE " + tableName + "SET "
				+ partialStr + " WHERE " + name[0] + "=@" + name[0];

			// Add parameters
			for (int i = 0; i < name.Length; i++)
			{
				cmd.Parameters.AddWithValue("@" + name[i], value[i]);
			}
			// Execute command
			int count = 0;
			using (con)
			{
				con.Open();
				count = cmd.ExecuteNonQuery();
			}

			if (count > 0)
				return true;
			else
				return false;//判断是否成功
		}

		/// <summary>
		/// 根据提供的连接字符串
		/// </summary>
		/// <param name="conStr">连接字符串</param>
		/// <returns></returns>
		public static SqlCommand GetSQLCommand(string conStr)
		{
			SqlConnection con = new SqlConnection(conStr);

			// Create command
			SqlCommand cmd = new SqlCommand();
			cmd.Connection = con;
			return cmd;
		}
		/// <summary>
		/// 更新数据库
		/// </summary>
		/// <param name="sql">SQL语句</param>
		/// <param name="conStr">连接字符串</param>
		public static bool Update(string sql, string conStr)
		{
			SqlCommand cmd = GetSQLCommand(conStr);
			SqlConnection con = cmd.Connection;
			cmd.CommandText = sql;
			int count = 0;
			using (con)
			{
				con.Open();
				count = cmd.ExecuteNonQuery();
			}

			if (count > 0)
				return true;
			else
				return false;//判断是否成功
		}


		/// <summary>
		/// 删除方法
		/// </summary>
		/// <param name="idName">ID属性名</param>
		/// <param name="id">删除数据的ID值</param>
		/// <param name="tableName">操作数据表的名字</param>
		/// <param name="conStr">连接字符串</param>
		public static bool deleteData(string idName, int id, string tableName, string conStr)
		{
			// Create connection
			//SqlConnection con = new SqlConnection(conStr);

			//// Create command
			//SqlCommand cmd = new SqlCommand();
			//cmd.Connection = con;
			SqlCommand cmd = GetSQLCommand(conStr);
			SqlConnection con = cmd.Connection;
			cmd.CommandText = "DELETE " + tableName + " WHERE " + idName + "=@" + idName;

			// Add parameters
			cmd.Parameters.AddWithValue("@" + idName, id);

			// Execute command
			int count = 0;
			using (con)
			{
				con.Open();
				count = cmd.ExecuteNonQuery();
			}

			if (count > 0)
				return true;
			else
				return false;//判断是否成功
		}
		public static bool deleteData(string sql, string conStr)
		{
			SqlCommand cmd = GetSQLCommand(conStr);
			SqlConnection con = cmd.Connection;
			cmd.CommandText = sql;
			int count = 0;
			using (con)
			{
				con.Open();
				count = cmd.ExecuteNonQuery();
			}

			if (count > 0)
				return true;
			else
				return false;//判断是否成功
		}

		//插入方法，传入参数依次为属性名字字段数组、属性值数组、操作数据表的名字、连接字符串
		/// <summary>
		/// 插入数据
		/// </summary>
		/// <param name="value">表全套属性的值</param>
		/// <param name="tablename">表的名字</param>
		/// <param name="conStr">连接字符串</param>
		public static bool insertData(string[] value, string tablename, string conStr)
		{
			string sql = "INSERT INTO " + tablename + " VALUES (";
			for (int i = 1; i <= value.Length; ++i)
			{
				if (i != 1)
					sql += ",";
				sql += "@" + i.ToString();
			}
			sql += ")";

			SqlCommand cmd = GetSQLCommand(conStr);
			SqlConnection con = cmd.Connection;
			cmd.CommandText = sql;
			for (int i = 1; i <= value.Length; ++i)
			{
				cmd.Parameters.AddWithValue("@" + i.ToString(), value[i - 1]);
			}
			int count = 0;
			using (con)
			{
				con.Open();
				count = cmd.ExecuteNonQuery();
			}

			if (count > 0)
				return true;
			else
				return false;//判断是否成功
		}

		/// <summary>
		/// 插入方法
		/// </summary>
		/// <param name="name">属性名字字段数组</param>
		/// <param name="value">属性值数组</param>
		/// <param name="tablename">操作数据表的名字</param>
		/// <param name="conStr">连接字符串</param>
		public static bool insertData(string[] name, object[] value, string tablename, string conStr)
		{
			string sql = "INSERT INTO " + tablename + "(";

			for (int i = 0; i < value.Length; ++i)
			{
				if (i != 0)
					sql += ",";
				sql += name[i];
			}
			sql += ") VALUES (";
			for (int i = 0; i < value.Length; ++i)
			{
				if (i != 0)
					sql += ",";
				sql += "@" + name[i];
			}
			sql += ")";
			SqlCommand cmd = GetSQLCommand(conStr);
			SqlConnection con = cmd.Connection;
			cmd.CommandText = sql;
			for (int i = 0; i < value.Length; ++i)
			{
				cmd.Parameters.AddWithValue("@" + name[i], value[i]);
			}
			int count = 0;
			using (con)
			{
				con.Open();
				count = cmd.ExecuteNonQuery();
			}

			if (count > 0)
				return true;
			else
				return false;//判断是否成功
		}
		public static bool insertData(string sql, string conStr)
		{
			SqlCommand cmd = GetSQLCommand(conStr);
			SqlConnection con = cmd.Connection;
			cmd.CommandText = sql;
			int count = 0;
			using (con)
			{
				con.Open();
				count = cmd.ExecuteNonQuery();
			}

			if (count > 0)
				return true;
			else
				return false;//判断是否成功
		}

		/// <summary>
		/// 查询方法
		/// </summary>
		/// <param name="name">属性名字字段数组</param>
		/// <param name="condition">查询条件，没有WHERE</param>
		/// <param name="tablename">操作数据表的名字</param>
		/// <param name="conStr">连接字符串</param>
		/// <returns>查询得到的DataTable对象</returns>
		public static DataTable selectData(string[] name, string condition, string tablename, string conStr)
		{
			string sql = "SELECT ";
			if (name == null || name.Length == 0)
				sql += "* ";
			else
			{
				for (int i = 0; i < name.Length; ++i)
				{
					if (i != 0)
						sql += ",";
					sql += name[i];
				}
			}
			sql += " FROM " + tablename;
			if (condition != null && condition != "")
				sql += " WHERE " + condition;
			return selectData(sql, conStr);
		}
		public static DataTable selectData(string sql, string conStr)
		{
			SqlDataAdapter dad = new SqlDataAdapter(sql, conStr);
			SqlCommandBuilder builder = new SqlCommandBuilder(dad);

			DataTable dtblMovies = new DataTable();
			dad.Fill(dtblMovies);
			return dtblMovies;
		}
		public static string GetConnectionString()
		{
			return "Data Source = .\\SQLEXPRESS;Integrated Security = true; AttachDbFilename = |DataDirectory|\\MOVIE.mdf;User Instance = true;"; ;
		}
		public static int GetMaxID(string tablename, string conStr)
		{
			DataTable table = selectData("SELECT MAX(ID) AS ID FROM " + tablename, conStr);
			if (table.Rows.Count == 0)
				return -1;
			return (int)table.Rows[0]["ID"];
		}
		public static DataTable selectPatialData(string sql, int start, int size, string conStr)
		{
			SqlDataAdapter dad = new SqlDataAdapter(sql, conStr);
			SqlCommandBuilder builder = new SqlCommandBuilder(dad);

			DataTable dtblMovies = new DataTable();
			dad.Fill(start, size, dtblMovies);
			return dtblMovies;
		}
	}

	
}